﻿Imports System.Data
Imports System.Data.SqlClient
Public Class PlanoContasDAL
#Region "Métodos"
#Region "Incluir"
    Public Function InserirAlterar(ByVal ccc_codigo As Integer, ByVal descr As String, ByVal grupCod As Integer, ByVal tip As String, ByVal stat As Integer, ByVal usucod As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim codigo As Integer
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SPPlanoContas"
            cmd.Parameters.Add(New SqlParameter("@ccc_codigo", SqlDbType.Int)).Value = ccc_codigo
            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descr
            cmd.Parameters.Add(New SqlParameter("@codGrupo", SqlDbType.Int)).Value = grupCod
            cmd.Parameters.Add(New SqlParameter("@ccctipo", SqlDbType.VarChar)).Value = tip
            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = stat
            cmd.Parameters.Add(New SqlParameter("@usu_codigo", SqlDbType.Int)).Value = usucod
            codigo = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return codigo
    End Function
#End Region
    '#Region "Alterar"
    '    Public Function Alterar(ByVal descr As String, ByVal grupCod As Integer, ByVal tip As String, ByVal stat As Integer, ByVal codig As Integer) As Integer
    '        Dim cmd = New SqlCommand
    '        Dim codigo As Integer
    '        Try
    '            cmd.CommandText = "update Financeiro.Ofi_ClassCentroCusto set ccc_descricao=@descricao, sgr_codigo=@codGrupo, ccc_tipo=@ccctipo, sta_codigo=@status where ccc_codigo=@codig"
    '            cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = descr
    '            cmd.Parameters.Add(New SqlParameter("@codGrupo", SqlDbType.Int)).Value = grupCod
    '            cmd.Parameters.Add(New SqlParameter("@ccctipo", SqlDbType.VarChar)).Value = tip
    '            cmd.Parameters.Add(New SqlParameter("@status", SqlDbType.Int)).Value = stat
    '            cmd.Parameters.Add(New SqlParameter("@codig", SqlDbType.Int)).Value = codig
    '            codigo = objconexao.executaCmd(cmd)
    '        Catch ex As Exception
    '            Throw ex
    '        Finally
    '            cmd = Nothing
    '        End Try
    '        Return codigo
    '    End Function
    '#End Region
#Region "Excluir"
    Public Function excluir(ByVal codigo As Integer) As Integer
        Dim cmd = New SqlCommand
        Dim ret As Integer
        Try
            cmd.CommandText = "update Financeiro.Ofi_ClassCentroCusto set sta_codigo=3 where ccc_codigo=@codigo"
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            ret = objconexao.executaCmd(cmd)
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return ret
    End Function
#End Region
#End Region
#Region "Buscar"
    Public Function buscar(ByVal Descricao As String, tipo As Integer) As DataTable
        Dim tab = New DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            Select Case tipo
                Case 0  'Código
                    cmd.CommandText = "select * from vw_FinPlanodeContas where ccc_codigo = @codigo"
                    cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = Val(Descricao)
                Case 1 'Descrição
                    cmd.CommandText = "select * from vw_FinPlanodeContas where ccc_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao and sta_codigo <> 3"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 2 'Status
                    cmd.CommandText = "select * from vw_FinPlanodeContas where sta_descricao COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 3 'Tipo
                    cmd.CommandText = "select * from vw_FinPlanodeContas where ccc_tipo COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case 4 'vem de consulta plano de contas 
                    cmd.CommandText = "select * from vw_FinPlanodeContas where ccc_tipo COLLATE SQL_Latin1_General_CP1_CI_AI like @descricao and sta_codigo=1"
                    cmd.Parameters.Add(New SqlParameter("@descricao", SqlDbType.VarChar)).Value = "%" & Descricao & "%"
                Case Else
                    cmd.CommandText = "select * from vw_FinPlanodeContas where sta_codigo <> 3 order by ccc_tipo "
            End Select
            tab = objconexao.executaConsulta(cmd)
            tab.DefaultView.AllowNew = False
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
        Return tab
    End Function

    Function valida(ByVal nome As String, ByVal subtipo As Integer, ByVal codigo As Integer) As Boolean
        Dim cmd As SqlCommand
        Dim tab As DataTable
        Try
            cmd = New SqlCommand
            cmd.CommandText = "SELECT * FROM Financeiro.Ofi_ClassCentroCusto where ccc_descricao=@nome and sgr_codigo=@sgrcodigo and ccc_codigo <> @codigo"
            cmd.Parameters.Add(New SqlParameter("@nome", SqlDbType.VarChar)).Value = nome
            cmd.Parameters.Add(New SqlParameter("@sgrcodigo", SqlDbType.Int)).Value = subtipo
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = codigo
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False  'nao encontrou
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
#Region "Valida Exclusão"
    Function valExclusaoCliente(ByVal cod As Integer) As Boolean 'Clientes
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Vendas.Ofi_Cliente where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function valExclusaoFunci(ByVal cod As Integer) As Boolean 'Funcionários
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Vendas.Ofi_Funcionario where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function valExcCtasReceber(ByVal cod As Integer) As Boolean 'Contas a Receber
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try '
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasReceber where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function valExcCtasPagar(ByVal cod As Integer) As Boolean 'Contas a pagar
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from vw_FinContasPagar where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function valExPrestadores(ByVal cod As Integer) As Boolean 'Prestadores
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Financeiro.Ofi_Prestadores where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
    Function valExFornecedores(ByVal cod As Integer) As Boolean 'Fornecedores
        Dim tab As DataTable
        Dim cmd As SqlCommand
        Try
            cmd = New SqlCommand
            cmd.CommandText = "select * from Estoque.Ofi_Fornecedor where ccc_codigo = @codigo and sta_codigo <> 2 and sta_codigo <> 3 "
            cmd.Parameters.Add(New SqlParameter("@codigo", SqlDbType.Int)).Value = cod
            tab = objconexao.executaConsulta(cmd)
            If (tab.Rows.Count = 0) Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Throw ex
        Finally
            cmd = Nothing
        End Try
    End Function
#End Region
End Class
